import { Pool as MySQLPool, ResultSetHeader } from 'mysql2/promise';
import { Pool as PgPool } from 'pg';
import {
  DatabaseOperations,
  DatabaseOperationsFactory,
  BaseMySQLOperations,
  BasePostgreSQLOperations,
} from '../../utils/database-operations.js';
import {
  SysDict,
  SysDictData,
  CreateSysDictDto,
  CreateSysDictDataDto,
  UpdateSysDictDto,
  UpdateSysDictDataDto,
  SysDictQueryDto,
  SysDictDataQueryDto,
  BatchDeleteDto,
} from '../../entities/system/SysDict.js';

/**
 * SysDict特定的数据库操作接口
 * @description 扩展基础数据库操作，添加字典特有的查询和批量删除方法
 */
interface SysDictOperations extends DatabaseOperations<SysDict> {
  getByKey(dictKey: string): Promise<SysDict | null>;
  batchDelete(ids: number[]): Promise<boolean>;
}

/**
 * SysDictData特定的数据库操作接口
 * @description 扩展基础数据库操作，添加字典数据特有的查询和批量删除方法
 */
interface SysDictDataOperations extends DatabaseOperations<SysDictData> {
  getByDictKey(dictKey: string): Promise<SysDictData[]>;
  batchDelete(ids: number[]): Promise<boolean>;
}

/**
 * MySQL字典操作实现
 * @description 实现SysDict在MySQL数据库中的CRUD操作
 */
class MySQLSysDictOperations extends BaseMySQLOperations<SysDict> implements SysDictOperations {
  constructor(pool: MySQLPool) {
    super(pool);
  }

  async create(data: Omit<SysDict, 'id'>): Promise<number> {
    const now = Date.now();
    const sql = `INSERT INTO sys_dict (
                      dict_name, dict_key, dict_type, status, remark, is_sys, time, update_time, disabled
                  ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`;
    const params: (string | number | boolean | null)[] = [
      data.dict_name as string,
      data.dict_key as string,
      data.dict_type as number,
      (data.status || 0) as number,
      (data.remark || '') as string,
      (data.is_sys || 0) as number,
      now,
      now,
      0,
    ];

    const [result] = await this.pool.execute(sql, params);
    return (result as ResultSetHeader).insertId;
  }

  async getById(id: number): Promise<SysDict | null> {
    const sql = 'SELECT * FROM sys_dict WHERE id = ? AND disabled = 0';
    const [rows] = await this.pool.execute(sql, [id]);
    const results = rows as SysDict[];
    return results.length > 0 ? results[0] : null;
  }

  async update(id: number, data: Partial<SysDict>): Promise<boolean> {
    const updateData = { ...data, update_time: Date.now() };
    const fields = Object.keys(updateData)
      .map(key => `${key} = ?`)
      .join(', ');
    const values = Object.values(updateData);
    const sql = `UPDATE sys_dict SET ${fields} WHERE id = ?`;

    const [result] = await this.pool.execute(sql, [...values, id]);
    return (result as ResultSetHeader).affectedRows > 0;
  }

  async delete(id: number): Promise<boolean> {
    return await this.update(id, { disabled: 1, update_time: Date.now() });
  }

  async list(
    page: number,
    pageSize: number,
    conditions?: Partial<SysDict>
  ): Promise<{
    list: SysDict[];
    total: number;
    page: number;
    pageSize: number;
  }> {
    let whereClause = 'WHERE disabled = 0';
    const params: (string | number)[] = [];

    if (conditions) {
      Object.entries(conditions).forEach(([key, value]) => {
        if (value !== undefined && value !== null) {
          whereClause += ` AND ${key} = ?`;
          params.push(value as string | number);
        }
      });
    }

    const countSql = `SELECT COUNT(*) as total FROM sys_dict ${whereClause}`;
    const [countRows] = await this.pool.execute(countSql, params);
    const total = (countRows as { total: number }[])[0].total;

    const offset = (page - 1) * pageSize;
    const dataSql = `SELECT * FROM sys_dict ${whereClause} ORDER BY time DESC LIMIT ? OFFSET ?`;
    const [dataRows] = await this.pool.execute(dataSql, [...params, pageSize, offset]);

    return {
      list: dataRows as SysDict[],
      total,
      page,
      pageSize,
    };
  }

  async getByKey(dictKey: string): Promise<SysDict | null> {
    const sql = 'SELECT * FROM sys_dict WHERE dict_key = ? AND disabled = 0';
    const [rows] = await this.pool.execute(sql, [dictKey]);
    const results = rows as SysDict[];
    return results.length > 0 ? results[0] : null;
  }

  async batchDelete(ids: number[]): Promise<boolean> {
    const updateData = { disabled: 1, update_time: Date.now() };
    for (const id of ids) {
      await this.update(id, updateData);
    }
    return true;
  }
}

// PostgreSQL字典操作实现
class PostgreSQLSysDictOperations
  extends BasePostgreSQLOperations<SysDict>
  implements SysDictOperations
{
  constructor(pool: PgPool) {
    super(pool);
  }

  async create(data: Omit<SysDict, 'id'>): Promise<number> {
    const now = Date.now();
    const sql = `INSERT INTO sys_dict (
                      dict_name, dict_key, dict_type, status, remark, is_sys, time, update_time, disabled
                  ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING id`;
    const params: (string | number | boolean | null)[] = [
      data.dict_name as string,
      data.dict_key as string,
      data.dict_type as number,
      (data.status || 0) as number,
      (data.remark || '') as string,
      (data.is_sys || 0) as number,
      now,
      now,
      0,
    ];

    const result = await this.queryOne<{ id: number }>(sql, params);
    return result?.id || 0;
  }

  async getById(id: number): Promise<SysDict | null> {
    const sql = 'SELECT * FROM sys_dict WHERE id = $1 AND disabled = 0';
    return await this.queryOne<SysDict>(sql, [id]);
  }

  async update(id: number, data: Partial<SysDict>): Promise<boolean> {
    const updateData = { ...data, update_time: Date.now() };
    const fields = Object.keys(updateData)
      .map((key, index) => `${key} = $${index + 1}`)
      .join(', ');
    const values = Object.values(updateData);
    const sql = `UPDATE sys_dict SET ${fields} WHERE id = $${values.length + 1}`;

    const affectedRows = await this.executeUpdate(sql, [...values, id]);
    return affectedRows > 0;
  }

  async delete(id: number): Promise<boolean> {
    return await this.update(id, { disabled: 1, update_time: Date.now() });
  }

  async list(
    page: number,
    pageSize: number,
    conditions?: Partial<SysDict>
  ): Promise<{
    list: SysDict[];
    total: number;
    page: number;
    pageSize: number;
  }> {
    let whereClause = 'WHERE disabled = 0';
    const params: (string | number)[] = [];
    let paramIndex = 1;

    if (conditions) {
      Object.entries(conditions).forEach(([key, value]) => {
        if (value !== undefined && value !== null) {
          whereClause += ` AND ${key} = $${paramIndex}`;
          params.push(value as string | number);
          paramIndex++;
        }
      });
    }

    const countSql = `SELECT COUNT(*) as total FROM sys_dict ${whereClause}`;
    const countResult = await this.queryOne<{ total: string }>(countSql, params);
    const total = parseInt(countResult?.total || '0', 10);

    const offset = (page - 1) * pageSize;
    const dataSql = `SELECT * FROM sys_dict ${whereClause} ORDER BY time DESC LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`;
    const dataResult = await this.queryMany<SysDict>(dataSql, [...params, pageSize, offset]);

    return {
      list: dataResult,
      total,
      page,
      pageSize,
    };
  }

  async getByKey(dictKey: string): Promise<SysDict | null> {
    const sql = 'SELECT * FROM sys_dict WHERE dict_key = $1 AND disabled = 0';
    return await this.queryOne<SysDict>(sql, [dictKey]);
  }

  async batchDelete(ids: number[]): Promise<boolean> {
    const updateData = { disabled: 1, update_time: Date.now() };
    for (const id of ids) {
      await this.update(id, updateData);
    }
    return true;
  }
}

// MySQL字典数据操作实现
class MySQLSysDictDataOperations
  extends BaseMySQLOperations<SysDictData>
  implements SysDictDataOperations
{
  constructor(pool: MySQLPool) {
    super(pool);
  }

  async create(data: Omit<SysDictData, 'id'>): Promise<number> {
    const now = Date.now();
    const sql = `INSERT INTO sys_dict_data (
                      dict_key, name, value, style, ext,
                      \`order\`, status, remark, is_sys, time, update_time, disabled
                  ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
    const params: (string | number | boolean | null)[] = [
      data.dict_key as string,
      data.name as string,
      data.value as string,
      (data.style || '') as string,
      (data.ext || '') as string,
      (data.order || 0) as number,
      (data.status || 0) as number,
      (data.remark || '') as string,
      (data.is_sys || 0) as number,
      now,
      now,
      0,
    ];

    const [result] = await this.pool.execute(sql, params);
    return (result as ResultSetHeader).insertId;
  }

  async getById(id: number): Promise<SysDictData | null> {
    const sql = 'SELECT * FROM sys_dict_data WHERE id = ? AND disabled = 0';
    const [rows] = await this.pool.execute(sql, [id]);
    const results = rows as SysDictData[];
    return results.length > 0 ? results[0] : null;
  }

  async update(id: number, data: Partial<SysDictData>): Promise<boolean> {
    const updateData = { ...data, update_time: Date.now() };
    const fields = Object.keys(updateData)
      .map(key => `${key} = ?`)
      .join(', ');
    const values = Object.values(updateData);
    const sql = `UPDATE sys_dict_data SET ${fields} WHERE id = ?`;

    const [result] = await this.pool.execute(sql, [...values, id]);
    return (result as ResultSetHeader).affectedRows > 0;
  }

  async delete(id: number): Promise<boolean> {
    return await this.update(id, { disabled: 1, update_time: Date.now() });
  }

  async list(
    page: number,
    pageSize: number,
    conditions?: Partial<SysDictData>
  ): Promise<{
    list: SysDictData[];
    total: number;
    page: number;
    pageSize: number;
  }> {
    let whereClause = 'WHERE disabled = 0';
    const params: (string | number)[] = [];

    if (conditions) {
      Object.entries(conditions).forEach(([key, value]) => {
        if (value !== undefined && value !== null) {
          whereClause += ` AND ${key} = ?`;
          params.push(value as string | number);
        }
      });
    }

    const countSql = `SELECT COUNT(*) as total FROM sys_dict_data ${whereClause}`;
    const [countRows] = await this.pool.execute(countSql, params);
    const total = (countRows as { total: number }[])[0].total;

    const offset = (page - 1) * pageSize;
    const dataSql = `SELECT * FROM sys_dict_data ${whereClause} ORDER BY \`order\` ASC, time DESC LIMIT ? OFFSET ?`;
    const [dataRows] = await this.pool.execute(dataSql, [...params, pageSize, offset]);

    return {
      list: dataRows as SysDictData[],
      total,
      page,
      pageSize,
    };
  }

  async getByDictKey(dictKey: string): Promise<SysDictData[]> {
    const sql =
      'SELECT * FROM sys_dict_data WHERE dict_key = ? AND disabled = 0 ORDER BY `order` ASC';
    const [rows] = await this.pool.execute(sql, [dictKey]);
    return rows as SysDictData[];
  }

  async batchDelete(ids: number[]): Promise<boolean> {
    const updateData = { disabled: 1, update_time: Date.now() };
    for (const id of ids) {
      await this.update(id, updateData);
    }
    return true;
  }
}

// PostgreSQL字典数据操作实现
class PostgreSQLSysDictDataOperations
  extends BasePostgreSQLOperations<SysDictData>
  implements SysDictDataOperations
{
  constructor(pool: PgPool) {
    super(pool);
  }

  async create(data: Omit<SysDictData, 'id'>): Promise<number> {
    const now = Date.now();
    const sql = `INSERT INTO sys_dict_data (
                      dict_key, name, value, style, ext,
                      "order", status, remark, is_sys, time, update_time, disabled
                  ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) RETURNING id`;
    const params: (string | number | boolean | null)[] = [
      data.dict_key as string,
      data.name as string,
      data.value as string,
      (data.style || '') as string,
      (data.ext || '') as string,
      (data.order || 0) as number,
      (data.status || 0) as number,
      (data.remark || '') as string,
      (data.is_sys || 0) as number,
      now,
      now,
      0,
    ];

    const result = await this.queryOne<{ id: number }>(sql, params);
    return result?.id || 0;
  }

  async getById(id: number): Promise<SysDictData | null> {
    const sql = 'SELECT * FROM sys_dict_data WHERE id = $1 AND disabled = 0';
    return await this.queryOne<SysDictData>(sql, [id]);
  }

  async update(id: number, data: Partial<SysDictData>): Promise<boolean> {
    const updateData = { ...data, update_time: Date.now() };
    const fields = Object.keys(updateData)
      .map((key, index) => `${key} = $${index + 1}`)
      .join(', ');
    const values = Object.values(updateData);
    const sql = `UPDATE sys_dict_data SET ${fields} WHERE id = $${values.length + 1}`;

    const affectedRows = await this.executeUpdate(sql, [...values, id]);
    return affectedRows > 0;
  }

  async delete(id: number): Promise<boolean> {
    return await this.update(id, { disabled: 1, update_time: Date.now() });
  }

  async list(
    page: number,
    pageSize: number,
    conditions?: Partial<SysDictData>
  ): Promise<{
    list: SysDictData[];
    total: number;
    page: number;
    pageSize: number;
  }> {
    let whereClause = 'WHERE disabled = 0';
    const params: (string | number)[] = [];
    let paramIndex = 1;

    if (conditions) {
      Object.entries(conditions).forEach(([key, value]) => {
        if (value !== undefined && value !== null) {
          whereClause += ` AND ${key} = $${paramIndex}`;
          params.push(value as string | number);
          paramIndex++;
        }
      });
    }

    const countSql = `SELECT COUNT(*) as total FROM sys_dict_data ${whereClause}`;
    const countResult = await this.queryOne<{ total: string }>(countSql, params);
    const total = parseInt(countResult?.total || '0', 10);

    const offset = (page - 1) * pageSize;
    const dataSql = `SELECT * FROM sys_dict_data ${whereClause} ORDER BY "order" ASC, time DESC LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`;
    const dataResult = await this.queryMany<SysDictData>(dataSql, [...params, pageSize, offset]);

    return {
      list: dataResult,
      total,
      page,
      pageSize,
    };
  }

  async getByDictKey(dictKey: string): Promise<SysDictData[]> {
    const sql =
      'SELECT * FROM sys_dict_data WHERE dict_key = $1 AND disabled = 0 ORDER BY "order" ASC';
    return await this.queryMany<SysDictData>(sql, [dictKey]);
  }

  async batchDelete(ids: number[]): Promise<boolean> {
    const updateData = { disabled: 1, update_time: Date.now() };
    for (const id of ids) {
      await this.update(id, updateData);
    }
    return true;
  }
}

// 字典服务类
export class SysDictService {
  private dictOperations: SysDictOperations;
  private dictDataOperations: SysDictDataOperations;

  constructor() {
    // 使用DatabaseOperationsFactory创建数据库操作实例
    this.dictOperations = DatabaseOperationsFactory.create<SysDictOperations>(
      MySQLSysDictOperations,
      PostgreSQLSysDictOperations
    );
    this.dictDataOperations = DatabaseOperationsFactory.create<SysDictDataOperations>(
      MySQLSysDictDataOperations,
      PostgreSQLSysDictDataOperations
    );
  }

  // 字典管理方法
  async createDict(data: CreateSysDictDto): Promise<number> {
    const now = Date.now();
    const dictData: Omit<SysDict, 'id'> = {
      dict_name: data.dict_name,
      dict_key: data.dict_key,
      dict_type: data.dict_type,
      status: data.status ?? 0,
      remark: data.remark ?? '',
      is_sys: data.is_sys ?? 0,
      time: now,
      update_time: now,
      disabled: 0,
    };
    return await this.dictOperations.create(dictData);
  }

  async updateDict(id: number, data: UpdateSysDictDto): Promise<boolean> {
    return await this.dictOperations.update(id, data);
  }

  async deleteDict(id: number): Promise<boolean> {
    return await this.dictOperations.delete(id);
  }

  async batchDeleteDict(data: BatchDeleteDto): Promise<boolean> {
    return await this.dictOperations.batchDelete(data.ids);
  }

  async getDictById(id: number): Promise<SysDict | null> {
    return await this.dictOperations.getById(id);
  }

  async getDictByKey(dictKey: string): Promise<SysDict | null> {
    return await this.dictOperations.getByKey(dictKey);
  }

  async getDictList(
    page: number,
    pageSize: number,
    query?: SysDictQueryDto
  ): Promise<{ list: SysDict[]; total: number; page: number; pageSize: number }> {
    const result = await this.dictOperations.list(page, pageSize, query);
    return {
      list: result.list,
      total: result.total,
      page: result.page,
      pageSize: result.pageSize,
    };
  }

  // 字典数据管理方法
  async createDictData(data: CreateSysDictDataDto): Promise<number> {
    const now = Date.now();
    const dictDataData: Omit<SysDictData, 'id'> = {
      name: data.name,
      value: data.value,
      dict_key: data.dict_key,
      style: data.style ?? '',
      order: data.order ?? 0,
      status: data.status ?? 0,
      ext: data.ext ?? '',
      remark: data.remark ?? '',
      is_sys: data.is_sys ?? 0,
      time: now,
      update_time: now,
      disabled: 0,
    };
    return await this.dictDataOperations.create(dictDataData);
  }

  async updateDictData(id: number, data: UpdateSysDictDataDto): Promise<boolean> {
    return await this.dictDataOperations.update(id, data);
  }

  async deleteDictData(id: number): Promise<boolean> {
    return await this.dictDataOperations.delete(id);
  }

  async batchDeleteDictData(data: BatchDeleteDto): Promise<boolean> {
    return await this.dictDataOperations.batchDelete(data.ids);
  }

  async getDictDataById(id: number): Promise<SysDictData | null> {
    return await this.dictDataOperations.getById(id);
  }

  async getDictDataByKey(dictKey: string): Promise<SysDictData[]> {
    return await this.dictDataOperations.getByDictKey(dictKey);
  }

  async getDictDataList(
    page: number,
    pageSize: number,
    query?: SysDictDataQueryDto
  ): Promise<{ list: SysDictData[]; total: number; page: number; pageSize: number }> {
    const result = await this.dictDataOperations.list(page, pageSize, query);
    return {
      list: result.list,
      total: result.total,
      page: result.page,
      pageSize: result.pageSize,
    };
  }
}
